package com.rankeiot.developer.controller;

import com.rankeiot.core.anno.Auth;
import com.rankeiot.core.data.DataTable;
import com.rankeiot.core.data.response.Resp;
import com.rankeiot.core.util.CollectionUtil;
import com.rankeiot.core.util.StringUtil;
import com.rankeiot.developer.DevelopDataMenu;
import com.rankeiot.developer.domain.TableFieldMeta;
import com.rankeiot.developer.service.TableServices;
import lombok.RequiredArgsConstructor;
import org.sagacity.sqltoy.config.model.PKStrategy;
import org.sagacity.sqltoy.dao.LightDao;
import org.sagacity.sqltoy.model.QueryExecutor;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.ssssssss.magicapi.datasource.model.DataSourceInfo;
import org.ssssssss.magicapi.datasource.model.MagicDynamicDataSource;
import org.ssssssss.magicapi.datasource.service.DataSourceMagicDynamicRegistry;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

@Auth(DevelopDataMenu.DB)
@RestController
@RequestMapping("db")
@RequiredArgsConstructor
public class DataDbController {
    private final MagicDynamicDataSource magicDynamicDataSource;
    private final DataSourceMagicDynamicRegistry dataSourceRegistry;
    private final TableServices tableServices;
    private final LightDao dao;

    @GetMapping("all")
    public Resp list() {
        List<Map> result = new ArrayList<>();
        //添加默认数据源
        result.add(CollectionUtil.asMap("key", "default", "name", "默认数据源"));
        List<DataSourceInfo> dsList = dataSourceRegistry.mappings().stream().distinct().toList();
        for (DataSourceInfo dsi : dsList) {
            Map item = new HashMap();
            item.put("key", dsi.getKey());
            item.put("name", dsi.getName());
            item.put("id", dsi.getId());
            item.put("lock", dsi.getLock());
            result.add(item);
        }
        return Resp.of(result);
    }

    @PostMapping("execSql")
    public Resp execSql(String id, String sql) {
        DataSource ds = tableServices.getDataSource(id);
        if (ds == null) {
            Resp.fail("数据源错误");
        }
        QueryExecutor q = new QueryExecutor(sql);
        return Resp.of(dao.findByQuery(q));
    }

    @GetMapping("tables/{id}")
    public Resp getTables(@PathVariable("id") String id) {
        try (Connection conn = tableServices.getConnection(id)) {
            ResultSet rs = conn.getMetaData().getTables(conn.getCatalog(), conn.getSchema(), "%", new String[]{"TABLE","VIEW"});
            List<Map<String, String>> result = new ArrayList<>();
            while (rs.next()) {
                Map<String, String> item = new HashMap<>();
                String tableName = rs.getString("TABLE_NAME");
                String remark = rs.getString("REMARKS");
                String tableType = rs.getString("TABLE_TYPE");
                item.put("key", tableName);
                item.put("title", tableName);
                item.put("type",tableType);
                item.put("remark",remark);
                item.put("treeIcon","TABLE".equals(tableType)?"jeicon-general-table":"jeicon-view");
                result.add(item);
            }
            rs.close();
            return Resp.of(result);
        } catch (SQLException e) {
            e.printStackTrace();
            Resp.fail(e.getMessage());
        }
        return Resp.ok();
    }

    @GetMapping("fields/{id}/{table}")
    public Resp getFields(@PathVariable String id, @PathVariable String table) {
        try (Connection conn = tableServices.getConnection(id)) {
            DatabaseMetaData meta = conn.getMetaData();
            String catalog = conn.getCatalog();
            String schema = conn.getSchema();
            ResultSet tbRs = meta.getTables(catalog, schema, table, new String[]{"TABLE", "VIEW"});
            String tableType = "TABLE";
            if (tbRs.next()) {
                tableType = tbRs.getString("TABLE_TYPE");
            } else {
                tbRs.close();
                Resp.fail("数据库中没有表 '" + table + "'");
            }
            tbRs.close();
            List<Map<String,String>> result = new ArrayList<>();
            if ("VIEW".equals(tableType)) {
                Statement stm = conn.createStatement();
                ResultSet rs = stm.executeQuery("select * from " + table + " where 1=0");
                ResultSetMetaData rm = rs.getMetaData();
                int c = rm.getColumnCount();
                for (int i = 1; i <= c; i++) {
                    Map<String,String> item=new HashMap<>();
                    String columName=rm.getColumnName(i);
                    int dataType =rm.getColumnType(i);
                    String fieldType=tableServices.getSqlDataType(dataType);
                    item.put("key",columName);
                    item.put("fieldType",fieldType);
                    item.put("title", columName);
                    item.put("treeIcon","jeicon-field-text-o");
                    result.add(item);
                }
                rs.close();
            } else {
                //主键
                Set<String> primaryKeys = new HashSet<>();//dm.getPrimaryKeys();
                ResultSet rs = meta.getPrimaryKeys(catalog, schema, table);
                while (rs.next()) {
                    String cn = rs.getString("COLUMN_NAME");
                    primaryKeys.add(cn);
                }
                ResultSet colMetaRs = null;
                try {
                    colMetaRs = meta.getColumns(catalog, schema, table, "%");
                    //ResultSetMetaData rm = colMetaRs.getMetaData();
                  //  int columnCount = rm.getColumnCount();
                    while (colMetaRs.next()) {
                        Map<String,String> item=new HashMap<>();
                        String columName=colMetaRs.getString("COLUMN_NAME");
                        String remark=colMetaRs.getString("REMARKS");
                        int dataType = colMetaRs.getInt("DATA_TYPE");
                        String fieldType=tableServices.getSqlDataType(dataType);
                        item.put("key",columName);
                        item.put("fieldType",fieldType);
                        item.put("remark",remark);
                        item.put("title", columName);
                        item.put("treeIcon",primaryKeys.contains(columName)?"jeicon-table-key":"jeicon-field-text-o");
                        result.add(item);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    if (colMetaRs != null) {
                        colMetaRs.close();
                    }
                }
            }
            return Resp.of(result);
        } catch (SQLException e) {
            e.printStackTrace();
            Resp.fail(e.getMessage());
        }
        return Resp.ok();
    }
}
